import express from "express";
import mysql from "mysql2/promise";
import * as dbConfig from "../config/db-config.js";
import { migrateTable } from "../services/migrate-service.js";

const router = express.Router();

/**
 * @swagger
 * tags:
 *  name: Migrate
 * description: 数据迁移相关接口
 * @param {string} tableName - 要迁移的表名
 * @param {boolean} removeField - 是否移除不存在的字段
 * @param {boolean} isDeleteRedundant - 是否删除多余字段
 */
router.post("/", async (req, res) => {
  const {
    tableName,
    removeField = false,
    isDeleteRedundant = false,
    showLogs = false,
  } = req.body;
  if (!tableName) {
    return res
      .status(400)
      .json({ success: false, message: "缺少 tableName 参数" });
  }

  let sourceConn, targetConn;
  const sqlLogs = [];
  try {
    const config = await dbConfig.loadConfig();
    sourceConn = await mysql.createConnection(config.source);
    targetConn = await mysql.createConnection(config.target);

    // 封装 query 方法，记录 SQL
    const wrapQuery = (conn, tag) => {
      const origQuery = conn.query.bind(conn);
      conn.query = async function (sql, params) {
        sqlLogs.push({ tag, sql, params });
        return origQuery(sql, params);
      };
    };
    wrapQuery(sourceConn, "source");
    wrapQuery(targetConn, "target");

    // 开启目标库事务
    await targetConn.beginTransaction();
    let result;
    try {
      result = await migrateTable(sourceConn, targetConn, tableName, {
        removeField,
        isDeleteRedundant,
        sqlLogs,
      });
      await targetConn.commit();
    } catch (err) {
      await targetConn.rollback();
      throw err;
    }
    // 只筛选目标库的变更型SQL
    const changedSqlTypes =
      /^(insert|update|delete|alter|create|truncate|drop)\b/i;
    const targetChangedSqls = sqlLogs
      .filter(
        (log) => log.tag === "target" && changedSqlTypes.test(log.sql.trim())
      )
      .map((log) => ({ sql: log.sql, params: log.params }));
    if (showLogs) {
      res.json({ ...result, sqlLogs, targetChangedSqls });
    } else {
      res.json({ ...result });
    }
  } catch (err) {
    res.status(500).json({
      success: false,
      message: `迁移失败: ${err.message},具体报错：${JSON.stringify(err)}`,
    });
  } finally {
    if (sourceConn) await sourceConn.end();
    if (targetConn) await targetConn.end();
  }
});

// 批量迁移接口
/**
 * @swagger
 * /migrate/batch:
 *   post:
 *     summary: 批量迁移多个表
 *     tags: [Migrate]
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               tableNames:
 *                 type: array
 *                 items:
 *                   type: string
 *                 description: 要迁移的表名数组
 *               removeField:
 *                 type: boolean
 *                 description: 是否移除不存在的字段
 *               isDeleteRedundant:
 *                 type: boolean
 *                 description: 是否删除多余字段
 *     responses:
 *       200:
 *         description: 迁移结果
 */
router.post("/batch", async (req, res) => {
  const {
    tableNames,
    removeField = false,
    isDeleteRedundant = false,
  } = req.body;
  if (!Array.isArray(tableNames) || tableNames.length === 0) {
    return res
      .status(400)
      .json({ success: false, message: "缺少 tableNames 参数或格式不正确" });
  }

  let sourceConn, targetConn;
  try {
    const config = await dbConfig.loadConfig();
    sourceConn = await mysql.createConnection(config.source);
    targetConn = await mysql.createConnection(config.target);

    const results = [];
    for (const tableName of tableNames) {
      try {
        const result = await migrateTable(sourceConn, targetConn, tableName, {
          removeField,
          isDeleteRedundant,
        });
        results.push({ tableName, ...result });
      } catch (err) {
        results.push({
          tableName,
          success: false,
          message: `迁移失败: ${err.message}`,
          error: err,
        });
      }
    }
    res.json({ success: true, results });
  } catch (err) {
    res.status(500).json({
      success: false,
      message: `批量迁移失败: ${err.message},具体报错：${JSON.stringify(err)}`,
    });
  } finally {
    if (sourceConn) await sourceConn.end();
    if (targetConn) await targetConn.end();
  }
});

// 再写两个个接口
// 第一个个接口是 从 sys_apiengine 中获取所有的接口名称
// 第二个接口是 把 我选择的sys_apiengine的数据从源数据库迁移到目标表中，前端选择接口名称
router.get("/api-engines", async (req, res) => {
  console.log("获取 API 引擎列表");
  let sourceConn;
  try {
    const config = await dbConfig.loadConfig();
    sourceConn = await mysql.createConnection(config.source);

    const [rows] = await sourceConn.query(
      "SELECT * FROM sys_apiengine where IsDeleted = 0"
    );
    res.json({ success: true, data: rows });
  } catch (err) {
    res
      .status(500)
      .json({ success: false, message: `获取接口名称失败: ${err.message}` });
  } finally {
    if (sourceConn) await sourceConn.end();
  }
});

// 通用补字段函数
async function syncTableFields(sourceConn, targetConn, tableName) {
  const [srcCols] = await sourceConn.query(`SHOW COLUMNS FROM \`${tableName}\``);
  const [tgtCols] = await targetConn.query(`SHOW COLUMNS FROM \`${tableName}\``);
  const tgtColNames = tgtCols.map((col) => col.Field);
  for (const srcCol of srcCols) {
    if (!tgtColNames.includes(srcCol.Field)) {
      const addSql = `ALTER TABLE \`${tableName}\` ADD COLUMN \`${srcCol.Field}\` ${srcCol.Type} ${srcCol.Null === "NO" ? "NOT NULL" : ""} ${
        srcCol.Default !== null ? `DEFAULT '${srcCol.Default}'` : ""
      }`;
      await targetConn.query(addSql);
      console.log(`➕ [syncTableFields] 补充目标库 ${tableName} 字段: ${srcCol.Field}`);
    }
  }
}

router.post("/api-engines/migrate", async (req, res) => {
  const { apiList } = req.body;
  if (!Array.isArray(apiList) || apiList.length === 0) {
    return res
      .status(400)
      .json({ success: false, message: "缺少 apiList 参数或格式不正确" });
  }
  let sourceConn, targetConn;
  try {
    const config = await dbConfig.loadConfig();
    sourceConn = await mysql.createConnection(config.source);
    targetConn = await mysql.createConnection(config.target);
    const results = [];
    for (const apiName of apiList) {
      console.log(`迁移接口: ${apiName}`);
      try {
        const [rows] = await sourceConn.query(
          "SELECT * FROM sys_apiengine WHERE Id = ?",
          [apiName]
        );
        if (rows.length === 0) {
          results.push({
            apiName,
            success: false,
            message: `接口 ${apiName} 不存在`,
          });
          continue;
        }
        const apiData = rows[0];

        // 检查目标表是否存在
        const exists = await targetConn.query(
          "SHOW TABLES LIKE 'sys_apiengine'"
        );
        if (exists[0].length === 0) {
          await targetConn.query(
            "CREATE TABLE sys_apiengine LIKE sys_apiengine"
          );
        }

        // 补齐目标表缺失字段
        await syncTableFields(sourceConn, targetConn, "sys_apiengine");

        // 检查接口是否已存在
        const [checkRows] = await targetConn.query(
          "SELECT * FROM sys_apiengine WHERE Id = ?",
          [apiName]
        );
        if (checkRows.length > 0) {
          // 更新已存在的接口
          await targetConn.query("UPDATE sys_apiengine SET ? WHERE Id = ?", [
            apiData,
            apiName,
          ]);
          results.push({
            apiName,
            success: true,
            message: `接口 ${apiName} 已存在，已更新`,
          });
          continue;
        }
        // 插入数据到目标表
        await targetConn.query("INSERT INTO sys_apiengine SET ?", apiData);
        results.push({
          apiName,
          success: true,
          message: `已迁移接口 ${apiName}`,
        });
      } catch (err) {
        results.push({
          apiName,
          success: false,
          message: `迁移失败: ${err.message}`,
          error: err,
        });
      }
    }
    res.json({ success: true, results });
  } catch (err) {
    res.status(500).json({
      success: false,
      message: `批量迁移接口失败: ${err.message},具体报错：${JSON.stringify(
        err
      )}`,
    });
  } finally {
    if (sourceConn) await sourceConn.end();
    if (targetConn) await targetConn.end();
  }
});

export default router;
